package jp.ac.u_tokyo.dch.dao.front;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.xml.FrontInfo;
import jp.ac.u_tokyo.dch.model.xml.FrontItem;
import jp.ac.u_tokyo.dch.util.common.CommonUtil;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;


/**
* @version 1.1
*/
public class DataDisplayDetailDao {

	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public DataDisplayDetailDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}

	/**
	 * データ表示詳細テーブルからリストを取得する
	 * @param スキーマID
	 * @return 表示項目リスト
	 * @throws DBException
	 */
	public List<FrontItem> selectByKeyId(String id) throws DBException {

		List<FrontItem> list = new ArrayList<FrontItem>();
		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			String query = "SELECT data_display_detail.schemaid,col1,col2,col3,col4,data_display_detail.fieldkey,displayname,itemname,sort " +
					"FROM data_display_detail INNER JOIN field_master " +
					"on data_display_detail.fieldkey = field_master.fieldkey WHERE displaysid = ? order by data_display_detail.displayorder";

			ps = dbconn.prepareStatement(query);

			ps.setInt(1, Integer.valueOf(id));

			rs = ps.executeQuery();
			while (rs.next()){

				FrontItem frontItem = new FrontItem();
				frontItem.setSchemaid(rs.getString("schemaid"));
				frontItem.setCol1(rs.getString("col1"));
				frontItem.setCol2(rs.getString("col2"));
				frontItem.setCol3(rs.getString("col3"));
				frontItem.setCol4(rs.getString("col4"));
				frontItem.setKey(rs.getString("fieldkey"));
				frontItem.setCategory(CommonUtil.getSortName(rs.getString("sort")));
				frontItem.setName(rs.getString("displayname"));

				list.add(frontItem);
			}
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;

	}

	/**
	 * データ表示詳細テーブルに挿入する
	 * @param keyschemaid キースキーマID
	 * @param frontInfo フロント表示オブジェクト
	 * @return 挿入情報
	 * @throws DBException 例外
	 */
	public int[] insert(Integer displayId, FrontInfo frontInfo) throws DBException {

		CustomPreparedStatement ps = null;
		int result[] = null;
		try{
			String query = "INSERT INTO data_display_detail " +
					"(displaysid,schemaid,col1,col2,col3,col4,fieldkey,displayorder)" +
					" VALUES (?,?,?,?,?,?,?,?)";

			ps = dbconn.prepareStatement(query);
			int order = 1;
			for (FrontItem frontItem : frontInfo.getList()) {

				int index = 1;
				ps.setInt(index++, displayId);
				ps.setString(index++, frontItem.getSchemaid());
				ps.setString(index++, frontItem.getCol1());
				ps.setString(index++, frontItem.getCol2());
				ps.setString(index++, frontItem.getCol3());
				ps.setString(index++, frontItem.getCol4());
				ps.setString(index++, frontItem.getKey());
				ps.setInt(index++, order);
				order++;
				ps.addBatch();
			}
			result = ps.executeBatch();
		}catch(DBException dbe){
			dbe.printStackTrace();
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/***
	 * 削除する
	 * @param keyschemaid キースキーマID
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(String displaysid) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		try{
			String query = "DELETE FROM data_display_detail WHERE displaysid = ?";
			ps = dbconn.prepareStatement(query);
			ps.setInt(1, Integer.valueOf(displaysid));
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}
}
